package cn.bdqn.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cn.bdqn.bean.User;
import cn.bdqn.bean.UserType;
import cn.bdqn.dao.BaseDao;
import cn.bdqn.dao.UserDao;

public class UserDaoImpl implements UserDao {
	

	//新增用户
	@Override
	public int addUser(Connection con, User user) throws SQLException {
		int row = 0;
		PreparedStatement ps = null;
		String sql = "insert into users (id, userCode, password, userName, gender, birthdate, "
				+ " phone, address, typeNo, createDate, createBy ) "
				+ " values (sq_users_id.nextval,?,?,?,?,to_date(?,'yyyy-MM-dd'),?,?,?,sysdate,?) ";
		
		//执行sql语句
		Date birth = user.getBirthdate();
		if(birth !=null ){
			Object[] params = {user.getUserCode(), user.getPassword(),user.getUserName(),user.getGender(),
					new SimpleDateFormat("yyyy-MM-dd").format(birth), user.getPhone(), user.getAddress(),user.getTypeNo(),
					 user.getCreateBy()};
			
			row = BaseDao.execute(con, ps, sql, params);
		}
		//关闭ps资
		BaseDao.closeConnection( null, ps, null);
		return row;
	}
	//修改
	@Override
	public int updateUser(Connection con, User user) throws SQLException {
		int row = 0;
		PreparedStatement ps = null;
		StringBuffer sql = new StringBuffer();
		ArrayList<Object> paraList = new ArrayList<Object>();
		sql.append("update users set userCode=?, password=?, userName=?, "
				+ " gender=? ");
		//执行sql语句
		paraList.add(user.getUserCode());
		paraList.add(user.getPassword());
		paraList.add(user.getUserName());
		paraList.add(user.getGender());
		Date birth = user.getBirthdate();
		if(birth !=null ){
			sql.append(" , birthdate=to_date(?,'yyyy-MM-dd') ");
			paraList.add(new SimpleDateFormat("yyyy-MM-dd").format(birth));
		}
		//拼接其他条件
		sql.append(" , phone=?, address=?, typeNo=?, "
				+ " modifyDate=sysdate, modifyBy=?  where id=? ");
		paraList.add(user.getPhone());
		paraList.add(user.getAddress());
		paraList.add(user.getTypeNo());
		paraList.add(user.getModifyBy());
		paraList.add(user.getId());
		Object[] params = paraList.toArray();
		row = BaseDao.execute(con, ps, sql.toString(), params);
		//关闭ps资源
		BaseDao.closeConnection( null, ps, null);
		return row;
	}
	//删除
	@Override
	public int deleteUser(Connection con, User user) throws SQLException {
		int row = 0;
		PreparedStatement ps = null;
		String sql = "delete from users where id=?  ";
		//执行sql语句
		Object[] params = { user.getId() };
		row = BaseDao.execute(con, ps, sql, params);
		//关闭ps资源
		BaseDao.closeConnection( null, ps, null);
		return row;
	}
	
	/**
	 * 获取总记录数
	 * @throws SQLException 
	 */
	@Override
	public int getCount(Connection con, User user ) throws SQLException {
		int count = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		//查询总行数sql
		StringBuffer sql = new StringBuffer();
		ArrayList<Object> paraList = new ArrayList<Object>();
		//拼接查询条件
		sql.append("select count(1) count from users where 1=1 ");
		if(user.getUserName()!=null && user.getUserName()!=""){
			sql.append(" and userName like ? ");
			paraList.add("%"+user.getUserName()+"%");
		}
		Object [] params = paraList.toArray();
		//执行查询操作
		rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
		if(rs.next()){
			count = rs.getInt("count");
		}
		//关闭资源
		BaseDao.closeConnection( null, ps, rs);
		return count;
	}
	/**
	 * 获取根据用户名查询的分页列表
	 * 当用户没有输入时查询用户名不为空的全部数据
	 * @throws SQLException 
	 */
	@Override
	public List<User> getList(Connection con, User user, int pageIndex, int pageSize) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> list = new ArrayList<User>();
		//查询分页sql
		StringBuffer sql = new StringBuffer();
		ArrayList<Object> paraList = new ArrayList<Object>();
		sql.append("SELECT * FROM  (SELECT ROWNUM rn,t1.* FROM "
				+ " (select u.*, typeName from users u left join userType t on u.typeNo=t.typeNo where 1=1 ");
		//凭借查询条件
		if(user.getUserName()!=null && user.getUserName()!=""){
			sql.append(" and userName like ? ");
			paraList.add("%"+user.getUserName()+"%");
		}
		//拼接sql
		sql.append(" ORDER BY ID) t1 WHERE ROWNUM<=?) t2 WHERE rn >= ? ");
		int startWith = (pageIndex -1)  * pageSize + 1;
		int endWith = pageSize * pageIndex;
		paraList.add(endWith);
		paraList.add(startWith);
		
		Object [] params = paraList.toArray();
		//执行查询操作
		rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
		while(rs.next()){
			User u = new User();
			u.setId(rs.getInt("id"));
			u.setUserCode(rs.getString("userCode"));
			u.setPassword(rs.getString("password"));
			u.setUserName(rs.getString("userName"));
			u.setPhone(rs.getString("phone"));
			u.setBirthdate(rs.getDate("birthdate"));
			u.setAddress(rs.getString("address"));
			u.setGender(rs.getInt("gender"));
			u.setTypeNo(rs.getInt("typeNo"));
			u.setTypeName(rs.getString("typeName"));
			list.add(u);
		}
		//关闭资源
		BaseDao.closeConnection( null, ps, rs);
		return list;
	}
	/**
	 * 根据用户id获取用户的所有信息
	 * @throws SQLException 
	 */
	@Override
	public User getUserById(Connection con, int id) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = new User();
		
		//查询用户sql
		String sql = "select u.*, typeName from users u left join userType t on u.typeNo = t.typeNo  where id=?";
		Object [] params ={ id };
		//执行查询操作
		rs = BaseDao.execute(con, ps, rs, sql, params);
		if(rs.next()){
			
			user.setId(rs.getInt("id"));
			user.setUserCode(rs.getString("userCode"));
			user.setPassword(rs.getString("password"));
			user.setUserName(rs.getString("userName"));
			user.setPhone(rs.getString("phone"));
			user.setGender(rs.getInt("gender"));
			user.setBirthdate(rs.getDate("birthdate"));
			user.setAddress(rs.getString("address"));
			user.setTypeNo(rs.getInt("typeNo"));
			user.setTypeName(rs.getString("typeName"));
		}
		//关闭资源
		BaseDao.closeConnection( null, ps, rs);
		return user;
	}
	/**
	 * 获取用户类型的所有列表
	 */
	@Override
	public List<UserType> getUserTypeList(Connection con) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<UserType> list = new ArrayList<UserType>();
		//查询分页sql
		String sql = "select * from userType ";
		//执行查询操作
		rs = BaseDao.execute(con, ps, rs, sql);
		while(rs.next()){
			UserType userType = new UserType();
			userType.setTypeNo(rs.getInt("typeNo"));
			userType.setTypeName(rs.getString("typeName"));
			list.add(userType);
		}
		//关闭资源
		BaseDao.closeConnection( null, ps, rs);
		return list;
	}
	@Override
	public User login(Connection con, User user)
			throws SQLException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		User u = new User();
		StringBuffer sql = new StringBuffer();
		ArrayList<Object> paraList = new ArrayList<Object>();
		sql.append("select * from users where userCode=? ");
		paraList.add(user.getUserCode());
		//拼接字符串
		if( user.getPassword()!=null &&  user.getPassword()!=""){
			sql.append(" and password=? ");
			paraList.add(user.getPassword());
		}
		Object[] params = paraList.toArray();
		rs = BaseDao.execute(con, ps, rs, sql.toString(), params);
		if(rs.next()){
			u.setId(rs.getInt("id"));
			u.setUserCode(rs.getString("userCode"));
			u.setPassword(rs.getString("password"));
			u.setUserName(rs.getString("userName"));
			u.setPhone(rs.getString("phone"));
			u.setGender(rs.getInt("gender"));
			u.setBirthdate(rs.getDate("birthdate"));
			u.setAddress(rs.getString("address"));
			u.setTypeNo(rs.getInt("typeNo"));
		}
		return u;
	}
	
	
	

}
